use crate::errors::MyError;
use crate::models::{Note, NoteProfile};
use crate::utils::{gen_uuid, get_title};
use deadpool_postgres::Client;
//use log::debug;
use std::result::Result;

pub async fn insert_or_update_note(
    note: &Note,
    client: &Client,
    user_id: &str,
) -> Result<u32, MyError> {
    let id: u32 = if note.has_id() { note.id } else { 0 };
    let title = get_title(&note.content).unwrap_or("".to_string());

    if note.has_id() {
        let _ = client
            .execute(
                r#"update notes set content=$3
                from user_notes
                where notes.id=user_notes.note_id
                and user_notes.user_id=$4
                and notes.id=$1 and notes.version=$2"#,
                &[
                    &(id as i32),
                    &(note.version as i32),
                    &note.content,
                    &user_id,
                ],
            )
            .await?;
        let _ = client
            .execute(
                r#"update note_profiles set title=$2 
                from user_notes 
                where id=$1 
                and note_profiles.id=user_notes.note_id 
                and user_notes.user_id=$3"#,
                &[&(id as i32), &title, &user_id],
            )
            .await?;
    } else {
        //println!("insert note");
        let results = client
            .query(
                "insert into notes (version, content) values($1, $2) RETURNING id;",
                &[&(note.version as i32), &note.content],
            )
            .await?;
        let new_row = results.first().expect("error for get new row");

        let new_id = new_row.get::<&str, i32>("id");
        //println!("notes inserted:{}", new_id);
        let _ = client
            .execute(
                "insert into note_profiles (id, title) values($1, $2);",
                &[&new_id, &title],
            )
            .await?;
        let _ = client
            .execute(
                "insert into user_notes (note_id, user_id) values($1, $2);",
                &[&new_id, &user_id],
            )
            .await?;
        return Ok(new_id as u32);
    }

    Ok(id)
}

pub async fn get_notes(client: &Client, user_id: &str) -> Result<Vec<Note>, MyError> {
    Ok(client
        .query(
            r#"select id, version, content from notes
        inner join user_notes on notes.id=user_notes.note_id
        and user_notes.user_id=$1
            "#,
            &[&user_id],
        )
        .await?
        .iter()
        .map(|row| Note {
            id: row.get("id"),
            version: row.get("version"),
            content: row.get("content"),
        })
        .collect::<Vec<Note>>())
}

// 返回version最大那一个
pub async fn get_note(id: u32, client: &Client, user_id: &str) -> Result<Note, MyError> {
    let note_row = client
        .query_one(
            r#"
            SELECT id, version, content
            FROM (
              SELECT id, version, content,
                     ROW_NUMBER() OVER (PARTITION BY id ORDER BY version DESC) AS rn
              FROM notes inner join user_notes on notes.id = user_notes.note_id
              and user_notes.user_id=$2
              WHERE id=$1
            ) subquery
            WHERE rn = 1;
            "#,
            &[&(id as i32), &user_id],
        )
        .await?;

    Ok(Note {
        id: note_row.get::<&str, i32>("id") as u32,
        version: note_row.get::<&str, i32>("version") as u32,
        content: note_row.get("content"),
    })
}

pub async fn delete_note(id: u32, client: &Client, user_id: &str) -> Result<(), MyError> {
    let _ = client
        .execute(
            r#"update note_profiles set deleted=true
            from user_notes on note_profiles.id = user_notes.note_id
            where note_profiles.id=$1 and user_notes.user_id=$2"#,
            &[&(id as i32), &user_id],
        )
        .await?;

    Ok(())
}
pub async fn get_note_profiles(
    client: &Client,
    user_id: &str,
) -> Result<Vec<NoteProfile>, MyError> {
    println!("get_note_profiles");
    Ok(client
        .query(
            r#"select id, title from note_profiles 
            inner join user_notes on id = note_id
            where note_profiles.deleted=false and user_notes.user_id=$1;"#,
            &[&user_id],
        )
        .await?
        .iter()
        .map(|row| NoteProfile {
            id: row.get::<&str, i32>("id") as u32,
            title: row.get("title"),
        })
        .collect::<Vec<NoteProfile>>())
}
